package com.myMinistry.provider;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.util.Log;

import com.myMinistry.Helper;
import com.myMinistry.provider.MinistryContract.EntryType;
import com.myMinistry.provider.MinistryContract.Householder;
import com.myMinistry.provider.MinistryContract.Literature;
import com.myMinistry.provider.MinistryContract.LiteraturePlaced;
import com.myMinistry.provider.MinistryContract.LiteratureType;
import com.myMinistry.provider.MinistryContract.Pioneering;
import com.myMinistry.provider.MinistryContract.Publisher;
import com.myMinistry.provider.MinistryContract.Rollover;
import com.myMinistry.provider.MinistryContract.Time;

public class MinistryDatabase extends SQLiteOpenHelper {
	private static final String TAG = "MinistryDatabase";
	public static final String DATABASE_NAME = "myministry";
	
	/** NOTE: carefully update onUpgrade() when bumping database versions to make sure user data is saved. */
	private static final int VER_LAUNCH = 1;
    private static final int VER_ADD_RETURN_VISITS = 2;
    
    private static final int DATABASE_VERSION = VER_ADD_RETURN_VISITS;
    
    interface Tables {
    	String ENTRY_TYPES = "entryTypes";
    	String HOUSEHOLDERS = "householders";
    	String PLACED_LITERATURE = "literaturePlaced";
    	String TYPES_OF_LIERATURE = "literatureTypes";
    	String LITERATURE = "literatureNames";
    	String PUBLISHERS = "publishers";
    	String ROLLOVER = "rolloverMinutes";
    	String TIMES = "time";
    	String PIONEERING = "pioneering";
    }
    
    public MinistryDatabase(Context context) {
    	super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    	db.execSQL("CREATE TABLE " + Tables.ENTRY_TYPES + " ("
    			+ 		EntryType._ID			+ " INTEGER PRIMARY KEY AUTOINCREMENT"
    			+ "," + EntryType.PUBLISHER_ID	+ " INTEGER"
    			+ "," + EntryType.NAME			+ " CHAR(50)"
    			+ "," + EntryType.ACTIVE		+ " INTEGER"
    			+ "," + EntryType.STUDY			+ " INTEGER"
    			+ "," + EntryType.RV			+ " INTEGER"
    			+ "," + EntryType.RBC			+ " INTEGER"
    			+ "," + EntryType.SORT_ORDER	+ " INTEGER )");
    	
    	db.execSQL("CREATE TABLE " + Tables.HOUSEHOLDERS + " ("
    			+ 		Householder._ID				+ " INTEGER PRIMARY KEY AUTOINCREMENT"
    			+ "," + Householder.PUBLISHER_ID	+ " INTEGER"
    			+ "," + Householder.NAME			+ " CHAR(50)"
    			+ "," + Householder.ADDR			+ " CHAR(50)"
    			+ "," + Householder.URI_ID			+ " INTEGER"
    			+ "," + Householder.MOBILE_PHONE	+ " CHAR(50)"
    			+ "," + Householder.HOME_PHONE		+ " CHAR(50)"
    			+ "," + Householder.WORK_PHONE		+ " CHAR(50)"
    			+ "," + Householder.OTHER_PHONE		+ " CHAR(50)"
    			+ "," + Householder.ACTIVE			+ " INTEGER"
    			+ "," + Householder.STAR			+ " INTEGER DEFAULT 0"
    			+ "," + Householder.ENTRY_TYPE_ID	+ " INTEGER"
    			+ "," + Householder.STUDY			+ " INTEGER"
    			+ "," + Householder.RV				+ " INTEGER )");
    	
    	db.execSQL("CREATE TABLE " + Tables.PLACED_LITERATURE + " ("
    			+ 		LiteraturePlaced._ID			+ " INTEGER PRIMARY KEY AUTOINCREMENT"
    			+ "," + LiteraturePlaced.PUBLISHER_ID	+ " INTEGER"
    			+ "," + LiteraturePlaced.LITERATURE_ID	+ " INTEGER"
    			+ "," + LiteraturePlaced.HOUSEHOLDER_ID	+ " INTEGER"
    			+ "," + LiteraturePlaced.TIME_ID		+ " INTEGER"
    			+ "," + LiteraturePlaced.COUNT			+ " INTEGER"
    			+ "," + LiteraturePlaced.MAG_MONTH		+ " INTEGER"
    			+ "," + LiteraturePlaced.MAG_YEAR		+ " INTEGER"
    			+ "," + LiteraturePlaced.DATE			+ " CHAR(8) )");
    	
    	db.execSQL("CREATE TABLE " + Tables.LITERATURE + " ("
    			+ 		Literature._ID					+ " INTEGER PRIMARY KEY AUTOINCREMENT"
    			+ "," + Literature.PUBLISHER_ID			+ " INTEGER"
    			+ "," + Literature.NAME					+ " CHAR(50)"
    			+ "," + Literature.TYPE_OF_LIERATURE_ID	+ " INTEGER"
    			+ "," + Literature.ACTIVE				+ " INTEGER"
    			+ "," + Literature.WEIGHT				+ " INTEGER DEFAULT 1"
    			+ "," + Literature.SORT_ORDER			+ " INTEGER )");
    	
    	db.execSQL("CREATE TABLE " + Tables.TYPES_OF_LIERATURE + " ("
    			+ 		LiteratureType._ID				+ " INTEGER PRIMARY KEY AUTOINCREMENT"
    			+ "," + LiteratureType.PUBLISHER_ID		+ " INTEGER"
    			+ "," + LiteratureType.NAME				+ " CHAR(50)"
    			+ "," + LiteratureType.ACTIVE			+ " INTEGER"
    			+ "," + LiteratureType.SORT_ORDER		+ " INTEGER )");
    	
    	db.execSQL("CREATE TABLE " + Tables.PIONEERING + " ("
    			+ 		Pioneering._ID				+ " INTEGER PRIMARY KEY AUTOINCREMENT"
    			+ "," + Pioneering.PUBLISHER_ID		+ " INTEGER"
    			+ "," + Pioneering.TYPE_ID			+ " INTEGER"
    			+ "," + Pioneering.YEAR_START		+ " INTEGER"
    			+ "," + Pioneering.MONTH_START		+ " INTEGER"
    			+ "," + Pioneering.MONTHLY_HOURS	+ " INTEGER )");
    	
    	db.execSQL("CREATE TABLE " + Tables.PUBLISHERS + " ("
    			+ 		Publisher._ID		+ " INTEGER PRIMARY KEY AUTOINCREMENT"
    			+ "," + Publisher.NAME		+ " CHAR(150)"
    			+ "," + Publisher.ACTIVE	+ " INTEGER DEFAULT 1)");
    	
    	db.execSQL("CREATE TABLE " + Tables.ROLLOVER + " ("
    			+ 		Rollover._ID			+ " INTEGER PRIMARY KEY AUTOINCREMENT"
    			+ "," + Rollover.PUBLISHER_ID	+ " INTEGER"
    			+ "," + Rollover.DATE			+ " CHAR(10)"
    			+ "," + Rollover.MINUTES		+ " INTEGER )");
    	
    	db.execSQL("CREATE TABLE " + Tables.TIMES + " ("
    			+		Time._ID				+ " INTEGER PRIMARY KEY AUTOINCREMENT"
				+ "," + Time.PUBLISHER_ID	+ " INTEGER"
		    	+ "," + Time.HOUSEHOLDER_ID	+ " INTEGER"
    			+ "," + Time.ENTRY_TYPE_ID	+ " INTEGER"
				+ "," + Time.DATE			+ " CHAR(10)"
		    	+ "," + Time.TIME_START		+ " CHAR(8)"
				+ "," + Time.TIME_END		+ " CHAR(8)"
		    	+ "," + Time.TOTAL_HOURS	+ " REAL"
		    	+ "," + Time.RETURN_VISITS	+ " INTEGER DEFAULT 0)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.d(TAG, "onUpgrade() from " + oldVersion + " to " + newVersion);

        /** NOTE: This switch statement is designed to handle cascading database updates, starting at the current version and falling through
         *  to all future upgrade cases. Only use "break;" when you want to drop and recreate the entire database.
         */
        int version = oldVersion;
        
        switch (version) {
            case VER_LAUNCH:
                /** Version 2 added column for return visits. */
            	db.execSQL("ALTER TABLE " + Tables.TIMES + " ADD COLUMN " + Time.RETURN_VISITS + " INTEGER DEFAULT 0");
                version = VER_ADD_RETURN_VISITS;
        }
        
        Log.d(TAG, "after upgrade logic, at version " + version);
        if (version != DATABASE_VERSION) {
            Log.w(TAG, "Destroying old data during upgrade");

            db.execSQL("DROP TABLE IF EXISTS " + Tables.ENTRY_TYPES);
            db.execSQL("DROP TABLE IF EXISTS " + Tables.HOUSEHOLDERS);
            db.execSQL("DROP TABLE IF EXISTS " + Tables.LITERATURE);
            db.execSQL("DROP TABLE IF EXISTS " + Tables.PLACED_LITERATURE);
            db.execSQL("DROP TABLE IF EXISTS " + Tables.PUBLISHERS);
            db.execSQL("DROP TABLE IF EXISTS " + Tables.ROLLOVER);
            db.execSQL("DROP TABLE IF EXISTS " + Tables.TIMES);
            db.execSQL("DROP TABLE IF EXISTS " + Tables.TYPES_OF_LIERATURE);
            onCreate(db);
        }
    }
    
    public boolean importDatabase(String dbPath, String packageName) throws IOException {
    	/** Close the SQLiteOpenHelper so it will commit the created empty database to internal storage. */
    	close();
    	File newDb = new File(dbPath);
    	File oldDb = new File(Environment.getDataDirectory(), "/data/" + packageName + "/databases/" + MinistryDatabase.DATABASE_NAME);
    	
    	if (newDb.exists()) {
    		Helper.copyFile(new FileInputStream(newDb), new FileOutputStream(oldDb));
    		/** Access the copied database so SQLiteHelper will cache it and mark it as created. */
    		getWritableDatabase().close();
    		return true;
    	}
    	else
    		return false;
    }
}